<?php
class Cms_Read_Pgsql extends Cms_Adapter_Pgsql_Abstract
	implements Cms_Read_Interface
{
	/**
	 *	Return the list of tables on selected database
	 *	@return Zend_Paginator
	 */
	public function getTablesList($filters)
	{
		$sql = $this->_db->select()
			->from(
				array('c' => 'pg_class'),
				array('tablename' => 'c.relname'),
				'pg_catalog'
			)
			->join(
				array('n' => 'pg_namespace'),
				'n.oid = c.relnamespace',
				array(),
				'pg_catalog'
			)
			->joinLeft(
				array('d' => 'pg_description'),
				'd.objoid = c.oid',
				array('comment' => 'description'),
				'pg_catalog'
			)
			->where("c.relkind = 'r'")
			->where('n.nspname = ?', $this->getSchema(), 'string')
			->order('c.relname');
		
		if(!empty($filters)) {
			$sql->where('c.relname ILIKE ?', "%{$filters}%", 'string');
		}
		
		return Zend_Paginator::factory($sql);
	}
	
	public function getTableData(array $filters, $order = null,
		$direction = null
	) {
		// Zend_ Db_Table_Select
		$sql = $this->_db->select()
			->from($this->getTable(), array('*'), $this->getSchema());
		
		// Searches or default filters
		$this->_setFilters($sql, $filters);
		
		// Set order to the data
		$this->_orderData($sql, $order, $direction);
		
		return Zend_Paginator::factory($sql);
	}
	
	public function getTableRow(array $clauses)
	{
		$sql = $this->_db->select()
			->from($this->getTable(), array('*'), $this->getSchema());
		
		foreach($clauses as $column => $value) {
			$sql->where("$column = ?", $value);
		}
		
		return $this->_db->fetchRow($sql);
	}
	
	public function getTableAll(array $clauses)
	{
		$sql = $this->_db->select()
			->from($this->getTable(), array('*'), $this->getSchema());
		
		foreach($clauses as $column => $value) {
			$sql->where("$column = ?", $value);
		}
		
		return $this->_db->fetchAll($sql);
	}
	
	/**
	 *	Orderers parameters from table
	 *	
	 *	@return array	
	 */
	public function getTableOrderers()
	{
		$orderers = array();
		$orderersParams = $this->getTableParams('order');
		if(!empty($orderersParams)) {
			foreach($orderersParams as $key => $field) {
				$orderers[] = "$key $field";
			}
		} else {
			// Order ASC by representative column
			$orderers[] = $this->representativeTableColumn();
		}
		return $orderers;
	}
	
	public function getNmData($where, array $join)
	{
		$sql = $this->_db->select()
			->from(array($this->getTable()), array(), $this->getSchema())
			->join($join['from'], $join['on'], array('*'), $join['schema'])
			->where(
				"{$this->getSchema()}.{$this->getTable()}.".
				key($where).
				' = '.
				current($where)
			);
		
		// Order to the data
		$db = Cms::factory('Read', $join['from'], $join['schema']);
		
		$sql->order($db->getAdapter()->getTableOrderers());
		
		return $this->_db->fetchAll($sql);
	}
	
	/**
	 *	@TODO: Separar este metodo e usar uma classe somente para isso
	 * @param type $sql
	 * @param type $filters 
	 */
	private function _setFilters($sql, $filters)
	{
		// Get table filter params
		$defaultFilters = $this->getTableParams('filter');
		
		if($defaultFilters != false) {
			// Whatever happened the filter will be restricted and always the
			// same
			foreach($defaultFilters as $dColumn => $dFilter) {
				// If the column don't exists the error will be from DB
				$filters[$dColumn] = $dFilter;
			}
		}
		
		// Each filter...
		foreach($filters as $column => $where)
		{
			if(!empty($where))
			{
				// Primary key
				if($column == 'ID') {
					$clauses = array();
					foreach($this->getPrimaryKey() as $primary) {
						$clauses[] = $this->_db->quoteInto(
							$primary.' IN(?)',
							new Zend_Db_Expr($where),
							'string'
						);
					}
					$sql->where(implode(' OR ', $clauses));

				} else {
					$columnData = $this->getColumnsFromTable($column);
					
					// Column from own table
					if(!empty($columnData))
					{
						if(!empty($columnData['reference'])) {
							list($from, $to) = explode(
								' = ', $columnData['reference']
							);
							// schema.table.foreign_field
							list($fS, $fT, $fF) = explode('.', $from);
							
							// Seta um novo adaptador
							$model = Cms::factory('Read', $fT, $fS);
							
							$fkField = $model->getAdapter()
								->representativeTableColumn();
							
							// INNER JOIN
							$sql->join(
								$fT,
								$columnData['reference'],
								array(),
								$fS
							)
							->where(
								$fS.'.'.$fT.'.'.$fkField.' ILIKE ?',
								"%$where%",
								'string'
							);
								
						// Search for string
						} elseif(preg_match('/char/', $columnData['type'])) {
							$sql->where(
								$column.' ILIKE ?',
								"%$where%",
								'string'
							);
						
						// Common search
						} else {
							$sql->where($column.' = ?', $where);
						}
						
					// Many to many table =)
					} else {
						// In NM cases is not a column, but a table
						$tb = $column;
						
						// Foreach table that make reference to this table
						foreach($this->getDependentTables() as $dependence)
						{
							// Separate the values
							list(
								$from['schema'],
								$from['table'],
								$from['column']
							) = explode('.', $dependence['from']);
							
							// Change adapter params
							$db = Cms::factory(
								'Read', $from['table'], $from['schema']
							);
							
							// Get column from table setted
							$nmColumns = $db->getAdapter()
								->getColumnsFromTable();
							
							// Foreach column of this possible NM table
							foreach($nmColumns as $nmColumn)
							{
								// If isn't a foreign key pass ahead
								if(empty($nmColumn['reference'])) {
									continue;
								}
								
								list(
									$ref['schema'],
									$ref['table'],
								) = explode('.', $nmColumn['reference']);
								
								// If the table was found
								if($ref['table'] == $tb)
								{
									$dbRef = Cms::factory(
										'Read', $ref['table'], $ref['schema']
									);
									
									$sql->join(
										$from['table'],
										(
											$dependence['to'].' = '.
											$dependence['from']
										),
										array(),
										$from['schema']
									)
									->join(
										$ref['table'],
										$nmColumn['reference'],
										array(),
										$ref['schema']
									)
									->where(
										(
											$ref['table'].'.'.
											$dbRef->getAdapter()
												->representativeTableColumn().
											' ILIKE ?'
										),
										"%$where%",
										'string'
									);
									break;
									break;
								}
							}
						}
					}
				}
			} // End empty($where)
		}
	}
	
	/**
	 *	Give to the query the right ordenation by table params or GET param. At
	 *  last try we order the table by the representative column
	 */
	private function _orderData($sql, $order = null, $direction = null)
	{
		$ordereres = array();
		
		// Default ordenation from table
		if(empty($order)) {
			$ordereres = $this->getTableOrderers();
		} else {
			$column = $this->getColumnsFromTable($order);
			if($column != false)
			{
				// If IS a foreign key
				if(!empty($column['reference']))
				{
					// Get reference schema and table
					list($foreign['schema'], $foreign['table']) = explode(
						'.', $column['reference']
					);
					
					// Change db adapter
					$db = Cms::factory(
						'Read', $foreign['table'], $foreign['schema']
					);
					$repColumn = $db->getAdapter()->representativeTableColumn();
					
					// Verify if this table is not already in the clause
					$fromCols = $sql->getPart(Zend_Db_Select::FROM);
					if(
						!isset($fromCols[$foreign['table']]) ||
						(
							$fromCols[$foreign['table']]['schema'] !=
							$foreign['schema']
						)
					) {
						// Add the table to the clause
						$sql->join(
							$foreign['table'],
							$column['reference'],
							array(),
							$foreign['schema']
						);
					}
					
					$ordereres = array(
						"{$foreign['table']}.$repColumn $direction"
					);
				} else {
					$ordereres = array("{$this->getTable()}.$order $direction");
				}
			}
//			else {
//				// @TODO: Pensar em adicionar aqui uma mensagem do sistema
//				// avisando que campos muitos para muitos nao podem ordenar
//			}
		}
		$sql->order($ordereres);
	}
}